﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data;

namespace BT_web__nhom06.VanPhongDoan
{
    public partial class ThemHD_Excel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            HyperLink1.Visible = false;
        }

        protected void btnimport_Click(object sender, EventArgs e)
        {
            string excelContentType = "application/vnd.ms-excel";
            string excel2010 = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            if (!ful_excel.HasFile)
            {
                lbthongbao.Text = "Cần chọn file cần import!";
            }
            else
            {
                try
                {
                    if (ful_excel.PostedFile.ContentType != excelContentType && ful_excel.PostedFile.ContentType != excel2010)
                    { lbthongbao.Text = "Vui lòng chọn file excel!!"; }
                    else
                    {
                        string path = string.Concat(Server.MapPath("~/temp/" + ful_excel.FileName));
                        ful_excel.SaveAs(path);
                        //sau khi upload xong doc du lieu tu file nay!
                        string excelConnetionstring = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source ={0};Extended Properties = Excel 8.0", path);
                        OleDbConnection connection = new OleDbConnection();
                        connection.ConnectionString = excelConnetionstring;
                        OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                        connection.Open();
                        OleDbDataReader dr = command.ExecuteReader();
                        //sau khi lay du lieu => insert vao sql sqlrver
                        string sqlConnectionstring = @"Data Source=DONGPHA-PC;Initial Catalog=qldv;Integrated Security=True";
                        SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionstring);
                        bulkCopy.DestinationTableName = "HoatDongDoan";//ten bang duoc chen
                        bulkCopy.WriteToServer(dr);
                        lbthongbao.Text = "Import thành công";
                        HyperLink1.Visible = true;
                        connection.Close();
                    }
                }
                catch { lbthongbao.Text = "Đã có lỗi xảy ra trong quá trình import!!"; }
            }
        }
    }
}